CREATE VIEW [dbo].[vSoaInvoiceLineCash]
AS
SELECT s.InvoiceLineId,
       s.InvoiceId,
       s.LineNumber,
       s.ItemId,
       s.ShipToPartyId,
       s.Quantity,
       CAST(CASE WHEN s.[BILL_COPIES] > 0 THEN (s.[BILL_AMOUNT]/s.[BILL_COPIES]) ELSE s.[BILL_AMOUNT] END AS MONEY) AS UnitPrice,       
       (CASE WHEN s.[PROD_TYPE] != 'VOL' THEN s.[BILL_AMOUNT]
       ELSE
         (CASE WHEN s.[BALANCE] - ISNULL(up.[Amount],0) <= 0
            THEN PaymentAdjustmentAmount + ISNULL(up.[Amount], 0)
          ELSE
            s.[BILL_AMOUNT]
         END)
       END) AS ExtendedAmount,
       PaymentAdjustmentAmount,
       ISNULL(up.Amount,0) AS PendingPaymentAdjustmentAmount,
       (CASE WHEN s.[PROD_TYPE] = 'VOL' THEN
         (CASE WHEN s.[BALANCE] - ISNULL(up.[Amount],0) <= 0 THEN 0
         ELSE
          s.[BALANCE]  - ISNULL(up.[Amount], 0)
         END)
        ELSE
         (s.[BALANCE]  - ISNULL(up.[Amount],0))
       END) AS Balance,
       s.BILL_DATE
  FROM [dbo].[vSoaInvoiceLineCashBase] s
       LEFT JOIN [dbo].[vSoaPendingCashPaymentAdjustment] up ON  s.[ShipToPartyId] = up.[ST_ID] AND s.[ItemId] = up.[PRODUCT_CODE]

GO